#! /bin/bash
#dwd_to_dws_1d.daily.sh all/表名 [日期]
#1、判断参数是否传入
if [ $# -lt 1 ]
then
	echo "必须传入all/表名..."
	exit
fi
#2、判断日期是否传入
[ "$2" ] && datestr=$2 || datestr=$(date -d '-1 day' +%F)
#田瑶
dws_paper_score_page_1d_sql="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_paper_score_page_1d partition (dt='$datestr')
select paper_id,paper_title,page_score,count(1) paper_score_1d
from (select user_id,
             t1.paper_id,
             t2.paper_title,
             case
                 when paper_score >= 90 then '90-100'
                 when paper_score >= 80 then '80-90'
                 when paper_score >= 70 then '70-80'
                 when paper_score >= 60 then '60-70'
                 when paper_score >= 50 then '50-60'
                 when paper_score >= 40 then '40-50'
                 when paper_score >= 30 then '30-40'
                 when paper_score >= 20 then '20-30'
                 when paper_score >= 10 then '10-20'
                 when paper_score >= 0 then '0-10'
                 end      page_score,
             t1.dt
      from dwd_test_action_inc t1
               join ods_test_paper_full t2 on t1.paper_id = t2.id
      where t1.dt = '$datestr'
         or t2.dt = '$datestr'
     )t3
group by paper_id,paper_title,page_score;
"
dws_question_accuracy_1d_sql="
insert into table dws_question_accuracy_1d partition (dt='$datestr')
select question_id,
     count(if((is_correct=1),1,null)) / count(1)   question_accuracy_1d
from dwd_test_action_inc
where dt = '$datestr'
group by question_id;
"

#赖远玮
dws_course_paper_test_paper_1d_sql="
insert overwrite table dws_course_paper_test_paper_1d partition (dt='$datestr')
select paper_id,
       sum(paper_score)        total_score_1d,
       avg(paper_score)        avg_score_1d,
       sum(duration_sec)       total_duration_sec_1d,
       avg(duration_sec)       avg_duration_sec_1d,
       count(distinct user_id) total_user_1d
from (
         select distinct paper_id,
                         user_id,
                         paper_score,
                         duration_sec
         from dwd_test_action_inc
         where dt='$datestr'
     ) t
group by paper_id;
"
dws_course_course_test_course_1d_sql="
insert overwrite table dws_course_course_test_course_1d partition (dt ='$datestr')
select course_id,
       sum(ta.paper_id)        total_course_score_1d,
       avg(paper_score)        avg_course_score_1d,
       sum(duration_sec)       total_course_duration_sec_1d,
       avg(duration_sec)       avg_course_duration_sec_1d,
       count(distinct user_id) total_course_user_1d
from (
         select course_id,
                paper_id
         from dim_paper_full
         where dt = '$datestr'
         group by course_id, paper_id
     ) pf
         join (
    select distinct paper_id,
                    user_id,
                    paper_score,
                    duration_sec
    from dwd_test_action_inc
    where dt = '$datestr'
) ta
              on pf.paper_id = ta.paper_id
group by course_id;
"

dws_traffic_user_course_videoplay_1d="
use edu;
insert overwrite table dws_traffic_user_course_videoplay_1d partition(dt='$datestr')
select uid,
       course_name,
       if(finish_chapter_num >= cast(chapter_num as bigint),1,0) finish_course
from (
         select course_id,
                course_name,
                uid,
                chapter_num,
                sum(is_finish) finish_chapter_num

         from (
                  select uid,
                         id,
                         video_name,
                         course_id,
                         course_name,
                         chapter_id,
                         chapter_name,
                         chapter_num,
                         during_sec,
                         if(sum_play_sec >= during_sec * 0.9 and max_position_sec >= during_sec * 0.9, 1, 0) is_finish
                  from (
                           select uid,
                                  id,
                                  video_name,
                                  course_id,
                                  course_name,
                                  chapter_id,
                                  chapter_name,
                                  chapter_num,
                                  during_sec,
                                  sum(play_sec)     sum_play_sec,
                                  sum(position_sec) max_position_sec
                           from dwd_videoplay_full
                           where dt = '$datestr'
                           group by uid, id, video_name, course_id, course_name, chapter_id, chapter_name, chapter_num,
                                    during_sec
                       ) t1
              ) t2
         group by course_id, course_name, chapter_num, uid
     ) t3;
"
dws_traffic_user_chapter_videoplay_1d="
use edu;
insert overwrite table dws_traffic_user_chapter_videoplay_1d partition (dt = '$datestr')
select uid,
       id,
       video_name,
       course_id,
       course_name,
       chapter_id,
       chapter_name,
       chapter_num,
       during_sec,
       if(sum_play_sec >= during_sec * 0.9 and max_position_sec >= during_sec * 0.9, 1, 0) is_finish
from (
         select uid,
                id,
                video_name,
                course_id,
                course_name,
                chapter_id,
                chapter_name,
                chapter_num,
                during_sec,
                sum(play_sec)     sum_play_sec,
                sum(position_sec) max_position_sec
         from dwd_videoplay_full
         where dt = '$datestr'
         group by uid, id, video_name, course_id, course_name, chapter_id, chapter_name, chapter_num, during_sec
     ) t1;
"


dws_trade_all_order_1d_sql="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_trade_all_order_1d partition (dt='$datestr')
    select count(distinct user_id) order_users_num_1d,
           sum(order_count_1d) order_count_1d,
           sum(order_total_amount_1d) order_total_amount_1d
    from (
            select user_id,
            count(order_id) order_count_1d,
            sum(final_amount) order_total_amount_1d,dt
            from dwd_trade_order_detail_inc
            where dt = '$datestr'
            group by user_id,dt
             )t1
group by dt;
"
## 2、dws_trade_province_order_1d
dws_trade_province_order_1d_sql="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_trade_province_order_1d partition (dt ='$datestr')
    select province_id,
           province_name,
           count(distinct user_id) order_users_num_1d,
           sum(order_count_1d) order_count_1d,
           sum(order_total_amount_1d) order_total_amount_1d
    from (
            select province_id,
                   province_name,
                   user_id,
            count(order_id) order_count_1d,
            sum(final_amount) order_total_amount_1d,dt
            from dwd_trade_order_detail_inc
            where dt = '$datestr'
            group by user_id,province_id,province_name,dt
             )t1
group by province_id,province_name;
"


dws_trffic_category_user_videoplay_1d="
insert overwrite table dws_trffic_category_user_videoplay_1d partition(dt ='$datestr')
select
    ch.category_id,
    ch.category_name,
    vi.user_id,
    count(1)
from (
         select
             uid user_id,
            chapter_id
         from dwd_videoplay_full
         where dt = '$datestr'
     )vi left join (
    select
        category_id,
        category_name,
        chapter_id
    from dim_chapter_full
    where dt = '$datestr'
) ch on vi.chapter_id = ch.chapter_id
group by category_id, category_name, user_id;
"


dws_trffic_subject_user_videoplay_1d="
insert overwrite table dws_trffic_subject_user_videoplay_1d partition (dt = '$datestr')
select
    subject_id,
    subject_name,
    category_id,
    category_name,
    user_id,
    count(1)
from (
         select
             uid user_id,
            chapter_id
         from dwd_videoplay_full
         where dt = '$datestr'
     )vi left join (
    select
        category_id,
        category_name,
        subject_id,
        subject_name,
        chapter_id
    from dim_chapter_full
    where dt = '$datestr'
) ch on vi.chapter_id = ch.chapter_id
group by subject_id, subject_name, category_id, category_name, user_id;
"

dws_trffic_course_user_videoplay_1d="
insert overwrite table dws_trffic_course_user_videoplay_1d partition (dt = '$datestr')
select
    course_id,
    course_name,
    subject_id,
    subject_name,
    category_id,
    category_name,
    user_id,
    count(1)
from (
         select
            uid user_id,
            chapter_id
         from dwd_videoplay_full
         where dt = '$datestr'
     )vi left join (
    select
        course_id,
        chapter_id,
        course_name,
        category_id,
        category_name,
        subject_id,
        subject_name
    from dim_chapter_full
    where dt = '$datestr'
) ch on vi.chapter_id = ch.chapter_id
group by course_id, course_name, subject_id, subject_name, category_id, category_name, user_id;
"

dws_traffic_session_page_order_1d_sql="insert overwrite table dws_traffic_session_page_order_1d partition(dt='$datestr')
select t1.session_id,
       mid_id,
       brand,
       model,
       operate_system,
       version_code,
       t1.channel,
       page_item_type,
       t1.user_id,
       order_status,
       sum(final_amount)
from  dwd_traffic_page_view_inc t1
         left join dwd_payment_info_inc t2
                    --两个事实表关联必须保证事实发生在同一天，根据用户id和用户当天下单的行为，必须保证日期是同一天
                   on t1.user_id = t2.user_id and t1.dt =t2.dt
where t1.dt = '$datestr'
  and t1.session_id is not null
  and t2.session_id is not null
group by channel, t1.session_id, mid_id, brand, model, operate_system, version_code,t1.user_id,order_status,page_item_type
;"

dws_traffic_session_page_view_1d_sql="insert overwrite table dws_traffic_session_page_view_1d partition(dt='$datestr')
select
    session_id,
    mid_id,
    brand,
    model,
    operate_system,
    version_code,
    channel,
    sum(during_time) during_time_1d,
    count(*) page_count_1d
from dwd_traffic_page_view_inc
where dt='$datestr' and session_id is not null
group by session_id,mid_id,brand,model,operate_system,version_code,channel;"

dws_course_order_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_course_order_1d partition(dt='$datestr')
select
    course_id,
    course_name,
    count(distinct(order_id)),
    sum(origin_amount ),
    sum(coupon_reduce ),
    sum(final_amount  ),
    count(nvl(distinct user_id,0))
from dwd_trade_order_detail_inc
where dt='$datestr'
group by course_id,course_name;
"

dws_course_review_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_course_review_1d partition(dt='$datestr')
select t1.course_id,
       user_review_sum_1d,
       user_review_count_1d,
       user_review_sum_1d/user_review_count_1d user_avg_review_1d,
       good_review_count_1d/user_review_count_1d positive_ratings_1d
from (select
          course_id,
          sum(review_stars) user_review_sum_1d,
          count(distinct (user_id)) user_review_count_1d,
          count(if(review_stars="5",1,0)) good_review_count_1d
      from dwd_course_evaluate_inc
      where dt='$datestr'
      group by course_id
     ) t1;
"

dws_trade_course_category_order_1d_sql="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_trade_course_category_order_1d partition (dt='$datestr')
select
    category_id,
    category_name,
    count(distinct order_id) order_count_1d,
    count(distinct user_id) order_num_1d,
    sum(origin_amount) order_original_amount_1d,
    sum(nvl(coupon_reduce,0.0)) coupon_reduce_amount_1d,
    sum(final_amount) order_total_amount_1d
from dwd_trade_order_detail_inc
where dt = '$datestr'
group by  category_id, category_name;
"
## 2、dws_trade_subject_course_order_1d
dws_trade_subject_course_order_1d_sql="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_trade_subject_course_order_1d partition(dt='$datestr')
select
    subject_id,
    subject_name,
    count(distinct order_id) order_count_1d,
    count(distinct user_id) order_num_1d,
    sum(origin_amount) order_original_amount_1d,
    sum(nvl(coupon_reduce,0.0)) coupon_reduce_amount_1d,
    sum(final_amount) order_total_amount_1d
from dwd_trade_order_detail_inc
where dt = '$datestr'
group by  subject_id, subject_name;
"

dws_play_user_course_count_1d_sql="set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_play_user_course_count_1d partition (dt='$datestr')
select
      uid,
       id,
      chapter_name,
       course_name,
       count(1)  play_count_1d,
       avg(play_sec) avg_duration_sec_1d,
       count(distinct uid) uv_count_1d
from dwd_videoplay_full
where dt ='$datestr'
group by course_name,uid,id,chapter_name;"

dws_play_user_chapter_count_1d_sql="set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_play_user_chapter_count_1d partition (dt='$datestr')
select
      uid,
       id,
       chapter_name,
     course_name,
       count(1)  play_count_1d,
       avg(play_sec) avg_duration_sec_1d,
       count(distinct uid) uv_count_1d
from dwd_videoplay_full
where dt ='$datestr'
group by chapter_name,uid,id,course_name;
"


dws_user_age_order_1d="
insert overwrite table dws_user_age_order_1d partition (dt='$datestr')
select
    age_group,
    order_count
from (
         select distinct
                         age_group,
                         count(user_id) over (partition by age_group) order_count
         from (
                  select user_id,
                         case
                             when age >= 10 and age < 20 then '青少年'
                             when age >= 20 and age < 30 then '青年'
                             when age >= 30 and age < 40 then '中年'
                             when age >= 40 and age < 50 then '大龄中年'
                             when age >= 50 then '老年'
                              else '少年'
                             end age_group
                  from (
                           select user_id,
                                  date_format(birthday, 'yyyy-MM-dd'),
                                  year('$datestr') - year(date_format(birthday, 'yyyy-MM-dd')) age
                           from dwd_trade_order_detail_inc
                            where dt = '2022-06-15'
                       ) t1
              ) t2
    )
t3;
"
dws_trade_user_payment_1d="
insert overwrite table dws_trade_user_payment_1d partition(dt='$datestr')
select
    user_id,
    count(distinct(order_id)),
    sum(distinct(course_id)),
    sum(final_amount)
from dwd_trade_order_detail_inc
where dt='$datestr'
group by user_id;

"

dws_trade_user_cart_add_1d="
INSERT OVERWRITE TABLE dws_trade_user_cart_add_1d PARTITION (dt = '$datestr')
SELECT user_id,
       count(*) cart_add_count_1d
FROM dwd_trade_cart_add_inc
WHERE dt = '$datestr'
GROUP BY user_id ;

"

dws_traffic_page_visitor_page_view_1d="
INSERT OVERWRITE TABLE dws_traffic_page_visitor_page_view_1d PARTITION (dt = '$datestr')
SELECT
       mid_id,
       page_id,
       sum(during_time) during_time_1d,
       count(*) view_count_1d
FROM  dwd_traffic_page_view_inc
WHERE dt ='$datestr'
GROUP BY mid_id,page_id;
"



dws_trade_user_order_1d="
insert overwrite table dws_trade_user_order_1d partition(dt = '$datestr')
select
    user_id,
    count(distinct(order_id)) ,
    sum(origin_amount),
    sum(nvl(coupon_reduce,0)),
    sum(final_amount)
from dwd_trade_order_detail_inc
WHERE dt ='$datestr'
group by user_id;
"


#3、匹配加载
case $1 in
"all")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;	$dws_user_age_order_1d
	$dws_trade_user_payment_1d
	$dws_trade_user_cart_add_1d
	$dws_traffic_page_visitor_page_view_1d
	$dws_trade_user_order_1d$dws_play_user_chapter_count_1d_sql$dws_play_user_course_count_1d_sql$dws_trade_subject_course_order_1d_sql$dws_trade_course_category_order_1d_sql$dws_course_order_1d $dws_course_review_1d$dws_traffic_session_page_order_1d_sql;$dws_traffic_session_page_view_1d_sql$dws_trffic_category_user_videoplay_1d;$dws_trffic_subject_user_videoplay_1d;$dws_trffic_course_user_videoplay_1d$dws_trade_province_order_1d_sql$dws_trade_all_order_1d_sql$dws_traffic_user_course_videoplay_1d$dws_traffic_user_chapter_videoplay_1d$dws_course_paper_test_paper_1d_sql;$dws_course_course_test_course_1d_sql$dws_paper_score_page_1d_sql;$dws_question_accuracy_1d_sql;"
;;
"dws_play_user_course_count_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_play_user_course_count_1d_sql;"
;;
"dws_play_user_chapter_count_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_play_user_chapter_count_1d_sql;"
;;
"dws_paper_score_page_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_paper_score_page_1d_sql;"
;;
"dws_question_accuracy_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_question_accuracy_1d_sql;"
;;
"dws_course_paper_test_paper_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_course_paper_test_paper_1d_sql;"
;;
"dws_course_course_test_course_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_course_course_test_course_1d_sql;"
;;
"dws_traffic_user_course_videoplay_1d")
    /opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_traffic_user_course_videoplay_1d;"
;;
"dws_traffic_user_chapter_videoplay_1d")
   /opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_traffic_user_chapter_videoplay_1d;"
;;
"dws_trade_all_order_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trade_all_order_1d_sql;"
;;
"dws_trade_province_order_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trade_province_order_1d_sql;"
;;
"dws_trffic_category_user_videoplay_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trffic_category_user_videoplay_1d;"
;;
"dws_trffic_subject_user_videoplay_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trffic_subject_user_videoplay_1d;"
;;
"dws_trffic_course_user_videoplay_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trffic_course_user_videoplay_1d;"
;;
"dws_traffic_session_page_order_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_traffic_session_page_order_1d_sql;"
;;
"dws_traffic_session_page_view_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_traffic_session_page_view_1d_sql;"
;;
  "dws_course_order_1d" )
     /opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_course_order_1d;"
  ;;
"dws_course_review_1d" )
  /opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_course_review_1d;"
  ;;
"dws_trade_course_category_order_1d")
/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trade_course_category_order_1d_sql;"
;;
"dws_trade_province_order_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trade_subject_course_order_1d_sql;"
;;
"dws_user_age_order_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_user_age_order_1d"
;;
"dws_trade_user_payment_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trade_user_payment_1d"

;;
"dws_trade_user_cart_add_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trade_user_cart_add_1d"

;;
"dws_traffic_page_visitor_page_view_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_traffic_page_visitor_page_view_1d"

;;
"dws_trade_user_order_1d")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dws_trade_user_order_1d"
;;

esac
